﻿// Copyright (c) 2006 Antu Corporation。 
// 创建人  ：LinJian
// 创建时间：2006-09-04
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace Antu.Business.Business
{
    /// <!--
    /// 功能描述  : oracle系统表操作
    /// 创建人  : LinJian
    /// 创建时间: 2006-09-04
    /// -->
    public class ClsOracleSystemOperation
    {
        #region 获取指定表的所有列名及数据类型
        /// <summary>
        /// 获取指定表的所有列名及数据类型
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <returns></returns>
        public static DataTable GetFieldsNameOfTable(string strTableName)
        {
            Database dbTemp = BasicOperate._NetOfficeDataBase;

            string strSql = string.Format("select * from user_tab_columns where table_Name='{0}'",
                strTableName);

            DataSet dsSource = dbTemp.ExecuteDataSet(CommandType.Text, strSql);

            return dsSource.Tables[0].Copy();
        }

        /// <summary>
        /// 根据表名和列名获取字段对应的类型
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strColumnName">字段名</param>
        /// <returns></returns>
        public static string GetFieldsNameOfTable(string strTableName,string strColumnName)
        {
            Database dbTemp = BasicOperate._NetOfficeDataBase;

            string strSql = string.Format(@"select data_type from user_tab_columns where table_Name='{0}' 
                and COLUMN_NAME='{1}'",strTableName,strColumnName);

            object objSource = dbTemp.ExecuteScalar(CommandType.Text, strSql);

            return objSource.ToString();
        }

        /// <summary>
        /// 获取指定表的所有列名及数据类型
        /// </summary>
        /// <param name="DbParam">数据库操作类</param>
        /// <param name="strTableName">表名</param>
        /// <returns></returns>
        public static DataTable GetFieldsNameOfTable(Database DbParam,string strTableName)
        {
            string strSql = string.Format("select * from user_tab_columns where table_Name='{0}'",
                strTableName);

            DataSet dsSource = DbParam.ExecuteDataSet(CommandType.Text, strSql);

            return dsSource.Tables[0].Copy();
        }
        #endregion

        #region 获取所有表名
        /// <summary>
        /// 获取所有表名
        /// </summary>
        /// <returns></returns>
        public static List<string> GetAllTablesName()
        {
            Database dbTemp = BasicOperate._NetOfficeDataBase;

            string strSql = "select table_name from user_tables";

            List<string> lstReturn = new List<string>();

            using (DataSet dsSource = dbTemp.ExecuteDataSet(CommandType.Text, strSql))
            {
                foreach (DataRow drTemp in dsSource.Tables[0].Rows)
                {
                    lstReturn.Add(drTemp["table_name"].ToString());
                }
            }

            return lstReturn;
        }
        #endregion

        #region 获取临时表数据
        /// <summary>
        /// 获取指定表的数据
        /// </summary>
        /// <param name="strTableName">数据表名</param>
        /// <returns></returns>
        public static DataTable GetOneTableData(string strTableName)
        {
            Database dbTemp = BasicOperate._NetOfficeDataBase;
            string strSql = string.Format("select * from {0}", strTableName);
            DataTable dtReturn = new DataTable();

            using (DbCommand cmd = dbTemp.GetSqlStringCommand(strSql))
            {
                //dbTemp.AddInParameter(cmd, "table", DbType.String, strTableName);

                DataSet dsSource = dbTemp.ExecuteDataSet(cmd);
                dtReturn = dsSource.Tables[0].Copy();
                dtReturn.TableName = strTableName;
            }

            return dtReturn;
        }

        /// <summary>
        /// 获取指定表的数据
        /// </summary>
        /// <param name="dbParam">数据库操作类</param>
        /// <param name="dbTrans">事务对象</param>
        /// <param name="strTableName">数据表名</param>
        /// <returns></returns>
        public static DataSet GetOneTableData(Database dbParam, DbTransaction dbTrans, string strTableName)
        {
            string strSql = string.Format("select * from {0}", strTableName);
            DataSet dsSource = new DataSet();

            using (DbCommand cmd = dbParam.GetSqlStringCommand(strSql))
            {
                if (dbTrans != null)
                {
                    dsSource = dbParam.ExecuteDataSet(cmd, dbTrans);
                }
                else
                {
                    dsSource = dbParam.ExecuteDataSet(cmd);
                }

                dsSource.Tables[0].TableName = strTableName;
            }

            return dsSource;
        }

        /// <summary>
        /// 获取指定表的指定行记录
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strKeyName">主键名</param>
        /// <param name="strKey">主键值</param>
        /// <returns></returns>
        public static DataSet GetOneRowData(string strTableName, string strKeyName, string strKey)
        {
            string strSql = string.Format("select * from {0} where {1}='{2}'", strTableName,strKeyName, strKey);
            Database dbTemp = BasicOperate._NetOfficeDataBase;
            try
            {
                DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
                DataSet ds = dbTemp.ExecuteDataSet(dbCmd);
                ds.Tables[0].TableName = strTableName;
                return ds;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 获取指定表的指定行记录(两个主键)
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strKeyName1">主键名</param>
        /// <param name="strKey1">主键值</param>
        /// <param name="strKeyName2"></param>
        /// <param name="strKey2"></param>
        /// <returns></returns>
        public static DataSet GetOneRowData(string strTableName, string strKeyName1, string strKey1,string strKeyName2, string strKey2)
        {
            string strSql = string.Format("select * from {0} where {1}='{2}' and {3}='{4}'",
                strTableName, strKeyName1, strKey1, strKeyName2, strKey2);
            Database dbTemp = BasicOperate._NetOfficeDataBase;
            try
            {
                DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
                DataSet ds = dbTemp.ExecuteDataSet(dbCmd);
                ds.Tables[0].TableName = strTableName;
                return ds;
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region 获取access表数据
        /// <summary>
        /// 根据表名获取access的表数据
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <returns></returns>
        public static DataTable GetAccessTableData(string strTableName)
        {
            Database dbTemp = BasicOperate.CreateWebDataBase();
            string strSql = string.Format("select * from {0}", strTableName);

            DataSet dsSource = new DataSet();
            using (DbCommand cmd = dbTemp.GetSqlStringCommand(strSql))
            {
                dsSource = dbTemp.ExecuteDataSet(cmd);
            }

            return dsSource.Tables[0].Copy();
        }

        /// <summary>
        /// 根据表名获取access的表数据
        /// </summary>
        /// <param name="dbParam">数据库操作类</param>
        /// <param name="dbTrans">事务处理对象(可为null)</param>
        /// <param name="strTableName">表名</param>
        /// <returns></returns>
        public static DataSet GetAccessTableData(Database dbParam, DbTransaction dbTrans, string strTableName)
        {
            string strSql = string.Format("select * from {0}", strTableName);

            DataSet dsSource = new DataSet();
            using (DbCommand cmd = dbParam.GetSqlStringCommand(strSql))
            {
                if (dbTrans != null)
                {
                    dsSource = dbParam.ExecuteDataSet(cmd, dbTrans);
                }
                else
                {
                    dsSource = dbParam.ExecuteDataSet(cmd);
                }

                dsSource.Tables[0].TableName = strTableName;
            }

            return dsSource;
        } 
        #endregion

        #region 删除临时表的数据
        /// <summary>
        /// 删除指定表的数据
        /// </summary>
        /// <param name="strTableName">数据表名</param>
        /// <returns></returns>
        public static int DeleteOneTableData(string strTableName)
        {
            Database dbTemp = BasicOperate._NetOfficeDataBase;
            string strSql = string.Format("delete from {0}", strTableName);

            return dbTemp.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 删除指定表的数据
        /// </summary>
        /// <param name="dbParam">数据库操作类</param>
        /// <param name="dbTrans">事务对象</param>
        /// <param name="strTableName">数据表名</param>
        /// <returns></returns>
        public static int DeleteOneTableData(Database dbParam, DbTransaction dbTrans, string strTableName)
        {
            string strSql = string.Format("delete from {0}", strTableName);

            if (dbTrans != null)
            {
                return dbParam.ExecuteNonQuery(dbTrans, CommandType.Text, strSql);
            }
            else
            {
                return dbParam.ExecuteNonQuery(CommandType.Text, strSql);
            }
        }

        /// <summary>
        /// 删除指定表的数据
        /// </summary>
        /// <param name="dbParam">数据库操作类</param>
        /// <param name="dbTrans">事务对象</param>
        /// <param name="strTableName">数据表名</param>
        /// <param name="strCondition">删除条件</param>
        /// <returns></returns>
        public static int DeleteOneTableData(Database dbParam, DbTransaction dbTrans, string strTableName,string strCondition)
        {
            string strSql = string.Format("delete from {0}", strTableName);
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql += string.Format(" where {0}", strCondition);
            }

            if (dbTrans != null)
            {
                return dbParam.ExecuteNonQuery(dbTrans, CommandType.Text, strSql);
            }
            else
            {
                return dbParam.ExecuteNonQuery(CommandType.Text, strSql);
            }
        }
        #endregion

        #region 删除access表数据
        /// <summary>
        /// 根据表名删除access的表数据
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <returns></returns>
        public static int DeleteAccessTableData(string strTableName)
        {
            Database dbTemp = BasicOperate.CreateWebDataBase();
            string strSql = string.Format("delete from {0}", strTableName);

            return dbTemp.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 根据表名删除access的表数据
        /// </summary>
        /// <param name="dbParam">数据库操作类</param>
        /// <param name="dbTrans">事务处理对象(可为null)</param>
        /// <param name="strTableName">表名</param>
        /// <returns></returns>
        public static int DeleteAccessTableData(Database dbParam, DbTransaction dbTrans,
            string strTableName)
        {
            string strSql = string.Format("select * from {0}", strTableName);

            if (dbTrans != null)
            {
                return dbParam.ExecuteNonQuery(dbTrans,CommandType.Text, strSql);
            }
            else
            {
                return dbParam.ExecuteNonQuery(CommandType.Text, strSql);
            }
        }
        #endregion

        #region 获取access的架构信息
        /// <summary>
        /// 获取access的架构信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAccessSchemaTables()
        {
            Database dbTemp = BasicOperate.CreateWebDataBase();

            DbConnection con = dbTemp.CreateConnection();
            con.Open();
            DataTable dtTable = con.GetSchema("Tables");
            con.Close();

            return dtTable;
        } 
        #endregion

        #region 根据表集合获取dataset
        /// <summary>
        /// 根据表集合获取dataset
        /// </summary>
        /// <param name="lstTables">表名集合</param>
        /// <returns></returns>
        public static DataSet GetNeedExportData(List<string> lstTables)
        {
            DataSet dsReturn = new DataSet("ExportData");

            foreach (string strTableName in lstTables)
            {
                DataTable dtOne = GetOneTableData(strTableName);
                dsReturn.Tables.Add(dtOne);
            }

            return dsReturn;
        }
        #endregion
    }
}
